﻿using Lamex.Base;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace Lamex.mNC.Common
{
    public class SQLHelper
    {
        public static void Insert_Record(string BARCODE, string MACHINENO, int PROCESSQTY)
        {
            string sql_Insert = @" insert into [LamexMESManager].[dbo].[NC_PN_AL_Manufacture_Process_Record] ([BARCODE_NO]
      ,[MACHINE_NO]
      ,[PROCESS_QTY]
      ,[SCAN_TIME]
      ,[SCAN_USER])
      values ('" + BARCODE + "','" + MACHINENO + "'," + PROCESSQTY + ",GETDATE(),'" + Lamex.Common.Loginer.CurrentUser.Account + "')";
            BaseBuildSql.ExecuteNonQuery(sql_Insert);
        }
        public static int GetScanQty(string _QRCode, int OrderQty)
        {
            int qty = 0, qty_temp = 0;
            string sql = @"SELECT SUM(PROCESS_QTY) PROCESS_QTY FROM [LamexMESManager].[dbo].[NC_PN_AL_Manufacture_Process_Record] 
      WHERE BARCODE_NO like '" + _QRCode + "%'";
            DataTable dt = BaseBuildSql.GetDataTableBySql(sql);
            if (dt.Rows.Count > 0)
            {
                if (!string.IsNullOrEmpty(dt.Rows[0]["PROCESS_QTY"].ToString()))
                    qty_temp = Convert.ToInt32(dt.Rows[0]["PROCESS_QTY"].ToString());
                if (OrderQty > qty_temp)
                    qty = OrderQty - qty_temp;
            }
            return qty;
        }
        public static DataTable Insert_NC_Convert_IssueRecord(string ISSUE_NO, string ISSUE_FROM, string PART_OR_DWG_NO
            , string ISSUE_CATEGORY, string ISSUE_DESC, string MO_PART_QTY, string RESPONSIBLE_DEPT, string PERSON_IN_CHARGE)
        {
            string strDateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            string strUser = Lamex.Common.Loginer.CurrentUser.AccountName;
            string sql = @"INSERT INTO [LamexMESManager].[dbo].[NC_Convert_IssueRecord](
       [ISSUE_NO]
      ,[ISSUE_FROM]
      ,[SUBMITTER]
      ,[SUBMIT_TIME]
      ,[PART_OR_DWG_NO]
      ,[ISSUE_CATEGORY]
      ,[ISSUE_DESC]
      ,[MO_PART_QTY]
      ,[RESPONSIBLE_DEPT]
      ,[PERSON_IN_CHARGE]
      ,[UPDATE_TIME]
      ,[UPDATE_USER]
	  )
	  VALUES('" + ISSUE_NO + "'," +
   "'" + ISSUE_FROM + "'," +
   "'" + strUser + "'," +
   "'" + strDateTime + "'," +
   "'" + PART_OR_DWG_NO + "'," +
   "'" + ISSUE_CATEGORY + "'," +
   "'" + ISSUE_DESC + "'," +
   "'" + MO_PART_QTY + "'," +
   "'" + RESPONSIBLE_DEPT + "'," +
   "'" + PERSON_IN_CHARGE + "'," +
   "'" + strDateTime + "'," +
   "'" + strUser + "'" +
   ")";
            BaseBuildSql.ExecuteNonQuery(sql);
            //返回DataTable
            string sql2 = @"SELECT *  FROM [LamexMESManager].[dbo].[NC_Convert_IssueRecord] WHERE ISSUE_NO='"+ISSUE_NO+"'";
            DataTable dt = new DataTable();
            dt = BaseBuildSql.GetDataTableBySql(sql2);
            return dt;
        }

        public static void Update_NC_Convert_ProcessRecord(string strNumber, string IssueNo)
        {
            string strName;
            if (Regex.Matches(strNumber, "[a-zA-Z]").Count > 0)
                strName = "DWG_NO";
            else
                strName = "PART_NO";
            string sql = @"UPDATE [LamexMESManager].[dbo].[NC_Convert_ProcessRecord] SET ISSUE_NO='"+IssueNo+"' " +
                " WHERE " + strName + "='" + strNumber + "'";
            BaseBuildSql.ExecuteNonQuery(sql);
        }

        public static void Insert_NC_Convert_ProcessRecord_History(string strNumber)
        {
            string strName;
            if (Regex.Matches(strNumber, "[a-zA-Z]").Count > 0)
                strName = "DWG_NO";
            else
                strName = "PART_NO";

            string sql = @" SELECT * FROM  [LamexMESManager].[dbo].[NC_Convert_ProcessRecord]
  WHERE " + strName + "='" + strNumber + "'";
            DataTable dataTable = BaseBuildSql.GetDataTableBySql(sql);
            string sql_update = @"UPDATE [NC_Convert_ProcessRecord] SET PLANNING_START_TIME=GETDATE() 
  WHERE " + strName + "='" + strNumber + "'";
            if (dataTable.Rows.Count > 0)
            {
                //批量插入
                BaseBuildSql.ExcuteInsert(dataTable, "NC_Convert_ProcessRecord_History");
                //更新时间
                BaseBuildSql.ExecuteNonQuery(sql_update);
            }
        }
        public static void Update_NC_Convert_ProcessRecordByState(string Issue_From,string strNumber)
        {
            string sql;
            string strName;
            if (Regex.Matches(strNumber, "[a-zA-Z]").Count > 0)
                strName = "DWG_NO";
            else
                strName = "PART_NO";
            if (Issue_From=="图纸更改")          
                sql = @"UPDATE [LamexMESManager].[dbo].[NC_Convert_ProcessRecord] SET NC_STATE='改图纸',CONVERTED_TIME=NULL,CHECHED_TIME=NULL,
  CHECHED_ENGINEER=NULL,RELEASED_TIME=NULL,RELEASED_ENGINEER=NULL,LOCKED_TIME=NULL,LOCKED_USER=NULL
  WHERE " + strName + "='" + strNumber + "'";
            else
                sql = @"UPDATE [LamexMESManager].[dbo].[NC_Convert_ProcessRecord] SET NC_STATE='待修改',CONVERTED_TIME=NULL,CHECHED_TIME=NULL,
  CHECHED_ENGINEER=NULL,RELEASED_TIME=NULL,RELEASED_ENGINEER=NULL,LOCKED_TIME=NULL,LOCKED_USER=NULL
  WHERE " + strName + "='" + strNumber + "'";
            BaseBuildSql.ExecuteNonQuery(sql);
        }
        public static void Update_NC_Convert_IssueRecordByNo(string Issue_No,string REASON_AND_ACTION)
        {
            string sql = @" UPDATE [LamexMESManager].[dbo].[NC_Convert_IssueRecord] SET REASON_AND_ACTION='"+REASON_AND_ACTION+"',FINISHED_TIME=GETDATE()" +
                "  WHERE ISSUE_NO='"+Issue_No+"'";
            sql = sql + ";" + @"  UPDATE  [LamexMESManager].[dbo].[NC_Convert_ProcessRecord] SET NC_STATE='改图纸' WHERE ISSUE_NO = '"+Issue_No+"'";
            BaseBuildSql.ExecuteNonQuery(sql);
        }
    }
}
